package org.kl.bf.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.joda.time.DateTime;

/**
 * Excel数据读取工具类，POI实现，兼容Excel2003，及Excel2007
 * @Author hqm
 * @CreateTime 2014年4月24日 下午8:34:24
 * @Alter by Qken 2014-12-22 
 * 	1 增加获取题头
 */
public class ExcelReader {
	Workbook wb = null;
	List<String[]> dataList = new ArrayList<String[]>(100);
	String[] headers = null;

	public ExcelReader(String path) {
		try {
			InputStream inp = new FileInputStream(path);
			wb = WorkbookFactory.create(inp);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public ExcelReader(InputStream inp) {
		try {
			wb = WorkbookFactory.create(inp);
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 取Excel所有数据，包含header
	 * @return List<String[]>
	 */
	public List<String[]> getAllData(int sheetIndex) {
		FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
		int columnNum = 0, rowNum = 0;
		Sheet sheet = wb.getSheetAt(sheetIndex);
		if (sheet.getRow(0) != null) {
			columnNum = sheet.getRow(0).getLastCellNum() - sheet.getRow(0).getFirstCellNum();
		}
		if (columnNum > 0) {
			for (Row row : sheet) {
				rowNum++;
				if (rowNum == 1) {// 第一行计入表头
					headers = new String[columnNum];
					for (int i = 0; i < columnNum; i++) {
						Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
						headers[i] = cell.getStringCellValue().trim();
					}
					continue;
				}
				String[] singleRow = new String[columnNum];
				int n = 0;
				
				// 先读取第一列的数据，如果第一列为空，则跳过该行
				Cell cell0=row.getCell(0, Row.CREATE_NULL_AS_BLANK);
				switch (cell0.getCellType()) {
				case Cell.CELL_TYPE_BLANK:
					singleRow[0] = "";
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					singleRow[0] = Boolean.toString(cell0.getBooleanCellValue());
					break;
				// 数值
				case Cell.CELL_TYPE_NUMERIC:
					if (DateUtil.isCellDateFormatted(cell0)) {
						singleRow[0] = new DateTime(cell0.getDateCellValue()).toString("yyyy-MM-dd HH:mm:ss");
					} else {
						cell0.setCellType(Cell.CELL_TYPE_STRING);
						String temp = cell0.getStringCellValue();
						// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
						if (temp.indexOf(".") > -1) {
							singleRow[0] = String.valueOf(new Double(temp)).trim();
						} else {
							singleRow[0] = temp.trim();
						}
					}
					break;
				case Cell.CELL_TYPE_STRING:
					singleRow[0] = cell0.getStringCellValue().trim();
					break;
				case Cell.CELL_TYPE_ERROR:
					singleRow[0] = "";
					break;
				case Cell.CELL_TYPE_FORMULA:
					switch (evaluator.evaluateFormulaCell(cell0)) {
					case Cell.CELL_TYPE_BOOLEAN:
						singleRow[0] = "";
						break;
					case Cell.CELL_TYPE_BLANK:
						singleRow[0] = "";
						break;
					case Cell.CELL_TYPE_ERROR:
						singleRow[0] = "";
						break;
					case Cell.CELL_TYPE_FORMULA:
						singleRow[0] = "";
						break;
					case Cell.CELL_TYPE_STRING:
						singleRow[0] = cell0.getStringCellValue();
						if (singleRow[0] != null) {
							singleRow[0] = singleRow[n].replaceAll("#N/A", "").trim();
						}
						break;
					default:
						singleRow[0] = "";
						break;
					}
					break;
				default:
					break;
				}
				if("".equals(singleRow[0])){
					continue;
				}
				
				//读某一行的数据
				for (int i = 0; i < columnNum; i++) {
					Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_BLANK:
						singleRow[n] = "";
						break;
					case Cell.CELL_TYPE_BOOLEAN:
						singleRow[n] = Boolean.toString(cell.getBooleanCellValue());
						break;
					// 数值
					case Cell.CELL_TYPE_NUMERIC:
						if (DateUtil.isCellDateFormatted(cell)) {
							singleRow[n] = new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd HH:mm:ss");
						} else {
							cell.setCellType(Cell.CELL_TYPE_STRING);
							String temp = cell.getStringCellValue();
							// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
							if (temp.indexOf(".") > -1) {
								singleRow[n] = String.valueOf(new Double(temp)).trim();
							} else {
								singleRow[n] = temp.trim();
							}
						}
						break;
					case Cell.CELL_TYPE_STRING:
						singleRow[n] = cell.getStringCellValue().trim();
						break;
					case Cell.CELL_TYPE_ERROR:
						singleRow[n] = "";
						break;
					case Cell.CELL_TYPE_FORMULA://针对带公式的单元格，先计算单元格的值
						switch (evaluator.evaluateFormulaCell(cell)) {
						case Cell.CELL_TYPE_BOOLEAN:
							singleRow[n] = "";
							break;
						case Cell.CELL_TYPE_BLANK:
							singleRow[n] = "";
							break;
						case Cell.CELL_TYPE_ERROR:
							singleRow[n] = "";
							break;
						case Cell.CELL_TYPE_FORMULA://实际不会发生
							break;
						case Cell.CELL_TYPE_STRING:
							singleRow[n] = cell.getStringCellValue();
							if (singleRow[n] != null) {
								singleRow[n] = singleRow[n].replaceAll("#N/A", "").trim();
							}
							break;
						default:
							break;
						}
						/*cell.setCellType(Cell.CELL_TYPE_STRING);
						singleRow[n] = cell.getStringCellValue();
							if (singleRow[n] != null) {
								singleRow[n] = singleRow[n].replaceAll("#N/A", "").trim();
							}
						break;*/
					default:
						break;
					}
					n++;
				}
				dataList.add(singleRow);
			}
		}
		return dataList;
	}

	/**
	 * 返回Excel最大行index值，实际行数要加1
	 * 
	 * @return
	 */
	public int getRowNum(int sheetIndex) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		return sheet.getLastRowNum();
	}

	/**
	 * 返回数据的列数
	 * 
	 * @return
	 */
	public int getColumnNum(int sheetIndex) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		Row row = sheet.getRow(0);
		if (row != null && row.getLastCellNum() > 0) {
			return row.getLastCellNum();
		}
		return 0;
	}
	
	/**
	 * 获取题头
	 * @return
	 */
	public String[] getHeaders(){
		return this.headers;
	}

	/**
	 * 获取某一行数据
	 * 
	 * @param rowIndex
	 *            计数从0开始，rowIndex为0代表header行
	 * @return
	 */
	public String[] getRowData(int sheetIndex, int rowIndex) {
		String[] dataArray = null;
		if (rowIndex > this.getColumnNum(sheetIndex)) {
			return dataArray;
		} else {
			dataArray = new String[this.getColumnNum(sheetIndex)];
			return this.dataList.get(rowIndex);
		}

	}

	/**
	 * 获取某一列数据
	 * 
	 * @param colIndex
	 * @return
	 */
	public String[] getColumnData(int sheetIndex, int colIndex) {
		String[] dataArray = null;
		if (colIndex > this.getColumnNum(sheetIndex)) {
			return dataArray;
		} else {
			if (this.dataList != null && this.dataList.size() > 0) {
				dataArray = new String[this.getRowNum(sheetIndex) + 1];
				int index = 0;
				for (String[] rowData : dataList) {
					if (rowData != null) {
						dataArray[index] = rowData[colIndex];
						index++;
					}
				}
			}
		}
		return dataArray;
	}
	
}